package com.samson.empapp.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.samson.empapp.dao.RoleDao;
import com.samson.empapp.util.DbUtil;
import com.samson.empapp.dbo.RoleDbo;

public class RoleDao implements IRoleDao{
	static Logger logger = Logger.getLogger(RoleDao.class);

	@Override
	public String findById(int roleId) {
		String roleName = "";
		Connection con = DbUtil.getOracleXEConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sb = null;
		try {
			sb = new StringBuilder();
			sb.append("select ROLE_NAME as roleName ");
			sb.append("from ROLE ");
			sb.append("where ROLE_ID = ?");
			pstmt = con.prepareStatement(sb.toString());
			logger.info("Executing Query " + sb.toString());
			pstmt.setInt(1, roleId);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				roleName = rs.getString("roleName");
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
			logger.error("Exception" + e);
		} finally {
			DbUtil.closeDBConnection(pstmt, rs, con, sb);
		}

		return roleName;
	}

	@Override
	public List<RoleDbo> findAll() {
		List<RoleDbo> returnList = new ArrayList<RoleDbo>();
		RoleDbo dbo = null;
		Connection con = DbUtil.getOracleXEConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sb = null;
		try {
			sb = new StringBuilder();
			sb.append("select ROLE_NAME as roleName, ROLE_ID "
					+ " as roleId    from ROLE    ORDER by ROLE_NAME asc ");
			pstmt = con.prepareStatement(sb.toString());
			logger.info("Executing Query " + sb.toString());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				dbo = new RoleDbo();
				dbo.setRoleId(rs.getInt("roleId"));
				dbo.setRoleName(rs.getString("roleName"));
				returnList.add(dbo);
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
			logger.error("Exception" + e);
		} finally {
			DbUtil.closeDBConnection(pstmt, rs, con, sb);
		}

		return returnList;

	}



}
